using System;
using System.Collections;
using System.Text;
using System.IO;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;


namespace jointablerelation
{
    class jointablerelation
    {
        static ArrayList gJoinExprs = new ArrayList();

        public static Boolean dofindjoincondition(TLz_Node pnode, Boolean pIsLeafNode)
        {
            Boolean ret = true;
            TLzCustomExpression lcExpr;

            if (pnode is TLzCustomExpression)
            {
                lcExpr = pnode as TLzCustomExpression;
                switch (lcExpr.oper)
                {
                    case TLzOpType.Expr_subquery:
                        lcExpr.IsVisitSubTree = false;
                        break;
                    case TLzOpType.Expr_Comparison :
                        gJoinExprs.Add(lcExpr);
                        lcExpr.IsVisitSubTree = false;
                        break;
                    case TLzOpType.Expr_AND:
                        lcExpr.IsVisitSubTree = true;
                        break;
                }

            }
            return ret;
        }

        public static string findtablename(TSourceToken st)
        {
            if (st.RelatedToken.DBObjType == TDBObjType.ttObjTable)
            {
                return st.RelatedToken.AsText;
            }

            else if (st.RelatedToken.DBObjType == TDBObjType.ttObjTableAlias)
            {
                return st.RelatedToken.RelatedToken.AsText;
            }
            else
            {
                return st.AsText;
            }

        }

        public static void tableinexpr(TLzCustomExpression pExpr)
        {
            gJoinExprs.Clear();

            pExpr.PreOrderTraverse(dofindjoincondition);

            if (gJoinExprs.Count > 0)
            {
                
                for (int i = 0; i < gJoinExprs.Count; i++)
                {
                    TLzCustomExpression lcExpr =  gJoinExprs[i] as TLzCustomExpression;

                    if (lcExpr.lexpr == null) { continue; }
                    if (lcExpr.rexpr == null) { continue; }
                    TLzCustomExpression lexpr = lcExpr.lexpr as TLzCustomExpression;
                    TLzCustomExpression rexpr = lcExpr.rexpr as TLzCustomExpression;

                    if (lexpr.oper != TLzOpType.Expr_Attr) { continue; }
                    if (rexpr.oper != TLzOpType.Expr_Attr) { continue; }

                    TLz_Attr lattr = lexpr.lexpr as TLz_Attr;
                    TLz_Attr rattr = rexpr.lexpr as TLz_Attr;

                    Console.WriteLine("Table 1 {0}, column 1 {1}", findtablename(lattr.ObjectNameToken), lattr.ColumnNameToken.AsText);
                    Console.WriteLine("Table 2 {0}, column 2 {1}", findtablename(rattr.ObjectNameToken), rattr.ColumnNameToken.AsText);

                }
            }


        }

        public static void relationinsql(TSelectSqlStatement pSelect)
        {
            TLzJoinList joinlist = pSelect.JoinTables;
            if (joinlist.Count() == 1)
            {
                TLzJoin join = joinlist[0];
                if (join.JoinItems.Count() == 0)
                {
                    Console.WriteLine("no join found");
                    return;
                }

                foreach (TLzJoinItem joinitem in join.JoinItems)
                {
                    Console.WriteLine("\nTable relation in join");
                    tableinexpr(joinitem.JoinQual);
                }
            }
            else
            {
                // from tablea,tableb,tablec
                if (pSelect.WhereClause != null)
                {
                    tableinexpr(pSelect.WhereClause);
                }
            }
        
        }

        static void Main(string[] args)
        {
            string lcsqltext = "";

            if (args.Length == 0)
            {
                Console.WriteLine("{0} scriptfile", "jointablerelation");
                return;
            }


            string inputfile = args[0];

            #region  Reading from file
            try
            {
                StreamReader re = File.OpenText(inputfile);

                string input = null;
                while ((input = re.ReadLine()) != null)
                {
                    if (lcsqltext.Length > 0)
                    {
                        lcsqltext = lcsqltext + Environment.NewLine;
                    }
                    lcsqltext = lcsqltext + input;
                }
            }
            catch (Exception e)
            {
                // Let the user know what went wrong.
                Console.WriteLine("File could not be read:");
                Console.WriteLine(e.Message);
                return;
            }
            #endregion  Reading from file

            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVOracle);
            sqlparser.SqlText.Text = lcsqltext;
            sqlparser.Parse();
            if (sqlparser.ErrorCount != 0)
            {
                Console.WriteLine(sqlparser.ErrorMessages);
                return;
            }

            if (sqlparser.SqlStatements.Count() == 0)
            {
                Console.WriteLine("no sql found");
                return;
            }

            if (sqlparser.SqlStatements[0].SqlStatementType != TSqlStatementType.sstSelect)
            {
                Console.WriteLine("no select sql found");
                return;
            }

            TSelectSqlStatement select = sqlparser.SqlStatements[0] as TSelectSqlStatement;
            relationinsql(select);

            // check subquery 
            for (int i = 0; i < select.ChildNodes.Count(); i++)
            {
                if (select.ChildNodes[i] is TSelectSqlStatement)
                {
                    relationinsql(select.ChildNodes[i] as TSelectSqlStatement);
                }
            }

        }
    }
}
